CREATE TABLE [dbo].[JY_Require]
(
[JY_Require_ID] [int] NOT NULL IDENTITY(1, 1),
[JY_Require_Company_ID] [int] NULL,
[JY_Require_Job] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL,
[JY_Require_Work_Area] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL,
[JY_Require_Study_Level] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL,
[JY_Require_Number] [int] NULL,
[JY_Require_Profession] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL,
[JY_Require_Requirement] [ntext] COLLATE Chinese_PRC_CI_AS NULL,
[JY_Require_GetJob_Type] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL,
[JY_Require_Remark] [ntext] COLLATE Chinese_PRC_CI_AS NULL,
[JY_Require_Add_Date] [datetime] NULL,
[JY_Require_Status] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL,
[JY_Require_Add_People] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL,
[JY_Require_Linkman] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL,
[JY_Require_Check_Time] [datetime] NULL,
[JY_Require_GetJob_Content] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL,
[JY_Require_End_Date] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
ALTER TABLE [dbo].[JY_Require] ADD 
CONSTRAINT [PK_JY_Require] PRIMARY KEY CLUSTERED  ([JY_Require_ID]) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[trig_JY_Require] ON [dbo].[JY_Require] 
   FOR INSERT,DELETE,UPDATE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
    -- Insert statements for trigger here
	-- for delete
    delete from JOL_Require
	where JOL_Require_Company in
	(	
		select JOL_Require_Company 
		from JOL_Require, deleted as b,JOL_Employer as c,JY_Company as d
		where (JOL_Require.JOL_Require_Company = d.JY_Company_Login)
			and (b.JY_Require_Company_ID = d.JY_Company_ID)
	 )	
	
	-- for inserted
	-- 大迁
	insert into JOL_Require
		(
			JOL_Require_Company,JOL_Require_Job,
			JOL_Require_Work_Area,JOL_Require_Study_Level,
			JOL_Require_Profession,JOL_Require_Number,
			JOL_Require_GetJob_Type,JOL_Require_Linkman,
			JOL_Require_Contact,JOL_Require_EndTime,
			JOL_Require_Requirement,JOL_Require_Remark,
			JOL_Require_AddUser,JOL_Require_AddTime,
			JOL_Require_UpdateUser,JOL_Require_UpdateTime,
			JOL_Require_VerifyTime,JOL_Require_VerifyStatus,
			JOL_Require_VerifyUser,JOL_Require_IsSendEmail,
			JOL_Require_Student_Type,JOL_Require_ClickNumber,
			OnlyForSysu
		) 
	select	a.JY_Require_ID,a.JY_Require_Job,
			a.JY_Require_Work_Area,a.JY_Require_Study_Level,
			a.JY_Require_Profession,a.JY_Require_Number,
			a.JY_Require_GetJob_Type,a.JY_Require_Linkman,
			convert(varchar(50),a.JY_Require_GetJob_Content),a.JY_Require_End_Date,
			a.JY_Require_Requirement,a.JY_Require_Remark,
			'数据迁入',a.JY_Require_Add_Date,
			'数据迁入',a.JY_Require_Add_Date,
			a.JY_Require_Check_Time,a.JY_Require_Status,
			'数据迁入','false',
			1,100,
			'false'
	from JY_Require as a, inserted as b
	where a.JY_Require_ID = b.JY_Require_ID
	-- 小改	
	update JOL_Require
	set JOL_Require.JOL_Require_Company  = convert(varchar(50),d.JY_Company_Login)
	from JOL_Require,JY_Company as d
	where (JOL_Require.JOL_Require_Company = d.JY_Company_ID)

END
GO
